var express = require('express');
var mysql = require("mysql");
var app = express(),
    path = require("path"),
    application_root = __dirname,
    _ = require('./lib/underscore/underscore.js');
    _.str = require('./lib/underscore/underscore-string.js');

// Config
app.configure(function () {
    app.use(express.bodyParser());
    app.use(express.methodOverride());
    app.use(app.router);
    app.use(express.static(path.join(application_root, "../exttrainee")));
    app.use(express.errorHandler({ dumpExceptions: true, showStack: true }));
});

// CRUD operation CREATE
app.post('/globallogic/create', function(req, res){
    queryProcessing.createEmployee(req.body, res);
});
// CRUD operation READ
app.get('/globallogic', function(req, res){
//    res.send(queryProcessing.getData(req.param("text")));
    queryProcessing.getData(req.param("text"), req.param("leaf"), res);
});
// CRUD operation UPDATE
app.post('/globallogic/update', function(req, res){
    queryProcessing.updateEmployee(req.body, res);
});
// CRUD operation DELETE
app.post('/globallogic/delete', function(req, res){
    queryProcessing.deleteEmployee(req.body, res);
});

app.post('/globallogic/sql', function(req, res){
    queryProcessing.sqlQuery(req.body.sql, res);
});

app.get('/globallogic/companies', function(req, res){
    queryProcessing.getAllCompanies(res);
});

app.get('/globallogic/locations', function(req, res){
    queryProcessing.getAllLocations(res);
});

app.get('/globallogic/stages', function(req, res){
    queryProcessing.getAllStages(res);
});

app.get('/globallogic/teams', function(req, res){
    queryProcessing.getAllTeams(res);
});

app.post("/globallogic/create/team", function(req, res){
    queryProcessing.createTeam(req.body, res);
});

app.get('/globallogic/tree', function(req, res){
    res.send(queryProcessing.getTree());
});

app.post('/globallogic/security', function(req, res){
    res.send(queryProcessing.updateServerData(req.body.password));
});

app.listen(80);
console.log('Server is working! Listening on port 80');

var glData = {
    company: [],
    location: [],
    stages: [],
    teams: []
};

var queryProcessing = {

    updateServerData: function(password){

        var respond = {
            "success": true,
            "root": [],
            "message": ""
        };

        if (password == "test"){
            sqlProcessing._loadAllData();
            respond.message = "data was update";
        } else {
            respond.success = false;
            respond.message = "invalid password";
        }

        return respond;

    },

    sqlQuery: function(sql, res){

        sqlProcessing._sendQuery(sql, res);

    },

    updateEmployee: function(body, res){

        sqlProcessing.emplopyees.updateEmployee(body, res);

    },

    deleteEmployee: function(body, res){

        sqlProcessing.emplopyees.deleteEmployee(body, res);

    },

    createEmployee: function(body, res){

        sqlProcessing.emplopyees.createEmployee(body, res);

    },

    getTree: function(){

        var respond = {
            "success": true,
            "children": []
        };

        for (var locationNumber = 0; locationNumber < glData.location.length; locationNumber++){
            var tempLocation = {};
            tempLocation["text"] = glData.location[locationNumber].name;
            tempLocation["children"] = [];
            for (var stageNumber = 0; stageNumber < glData.stages.length; stageNumber++){
                var tempStage = {};
                if (glData.stages[stageNumber].location == tempLocation["text"]){
                    tempStage["text"] = glData.stages[stageNumber].name;
                    tempStage["children"] = [];
                    for (var teamNumber = 0; teamNumber < glData.teams.length; teamNumber++){
                        var tempTeam = {};
                        if (glData.teams[teamNumber].stage == tempStage["text"]){
                            tempTeam["text"] = glData.teams[teamNumber].name;
                            tempTeam["leaf"] = true;
                            tempStage["children"].push(tempTeam);
                        }
                    }
                    if (tempStage["children"].length > 0){
                        tempStage["expanded"] = true;
                    }
                    tempLocation["children"].push(tempStage);
                }
            }
            if (tempLocation["children"].length > 0){
                tempLocation["expanded"] = true;
            }
            respond.children.push(tempLocation);
        }

        return respond;

    },

    getAllCompanies: function(res){

        sqlProcessing.companies.getAllCompanies(res);

    },

    getAllLocations: function(res){

        sqlProcessing.locations.getAllLocations(res);

    },

    getAllStages: function(res){

        sqlProcessing.stages.getAllStages(res);

    },

    getAllTeams: function(res){

        sqlProcessing.teams.getAllTeams(res);

    },

    createTeam: function(team, res){

        sqlProcessing.teams.createTeam(team, res);
        setTimeout(sqlProcessing._loadAllData, 1000);

    },

    getData: function(text, leaf, res){

        if (leaf == "true"){
            sqlProcessing.emplopyees.getEployeesFromTeam(text, res);
        } else {
            var respond = {
                "success": true,
                "root": []
            };

            for (var i = 0; i < glData.stages.length; i++){
                if (glData.stages[i].location == text){
                    respond.root.push(glData.stages[i]);
                }
            }
            for (var i = 0; i < glData.location.length; i++){
                if (glData.location[i].company == text){
                    respond.root.push(glData.location[i]);
                }
            }
            for (var i = 0; i < glData.teams.length; i++){
                if (glData.teams[i].stage == text){
                    respond.root.push(glData.teams[i]);
                }
            }

            res.send(respond);
        }

    }
};

var sqlProcessing = {

    _loadAllData: function(){

        var connection = mysql.createConnection({
            user: "root",
            password: "brutal1ty",
            database: "globallogic"
        });

        connection.connect();
        connection.query("SELECT * FROM teams", function(err, rows, fields){
            if (err){
//                err
            } else {
                glData.teams = rows;
            }
        });
        connection.query("SELECT * FROM company", function(err, rows, fields){
            if (err){
//                err
            } else {
                glData.company = rows;
            }
        });
        connection.query("SELECT * FROM location", function(err, rows, fields){
            if (err){
//                err
            } else {
                glData.location = rows;
            }
        });
        connection.query("SELECT * FROM stages", function(err, rows, fields){
            if (err){
//                err
            } else {
                glData.stages = rows;
            }
        });
        connection.end();

    },

    _dataBaseSettings: {
        user: "root",
        password: "brutal1ty",
        database: "globallogic"
    },

    _sendQuery: function(sql, res, callback, msg){
        var connection = mysql.createConnection(sqlProcessing._dataBaseSettings);
        var respond = {
            "success": true,
            "root": [],
            "message": ""
        };

        var isQueryCompleted = false;

        connection.connect();
        connection.query(sql, function(err, rows, fields){
            if (err){
                respond.message = err.code;
                respond.success = false;
            } else {
                if (callback){
                    respond.root = callback(rows);
                } else {
                    respond.root = rows;
                }
                if (msg){
                    respond.message = msg;
                } else {
                    respond.message = respond.root.length + " rows";
                }
            }

            isQueryCompleted = true;
        });
        connection.end();

        function sendRespond(){
            setTimeout(function(){
                if (isQueryCompleted){
                    res.send(respond);
                } else {
                    sendRespond();
                }
            }, 100);
        };

        sendRespond();
    },

    emplopyees: {

        getAllEmployees: function(res){

            var sql = "SELECT * FROM employees";
            sqlProcessing._sendQuery(sql, res);

        },

        createEmployee: function(empl, res){

            var sql = "INSERT INTO employees (name,age,phone,married,team) VALUES ('" +
                empl.name + "'," + empl.age + ",'" + empl.phone + "'," + empl.married + ",'" + empl.team + "')";
            var msg = "Employee " + empl.name + " was create";
            sqlProcessing._sendQuery(sql, res, false, msg);

        },

        deleteEmployee: function(empl, res){

            var sql = "DELETE FROM employees WHERE id = " + empl.id;
            var msg = "Employee " + empl.name + " was delete";
            sqlProcessing._sendQuery(sql, res, false, msg);

        },

        getEployeesFromTeam: function(team, res){

            var sql = "SELECT * FROM employees WHERE team = '" + team + "'";
            var msg = "Employees from " + team;
            sqlProcessing._sendQuery(sql, res, false, msg);

        },

        updateEmployee: function(empl, res){

            var sql = "UPDATE employees SET " +
                "name = '" + empl.name + "', " +
                "age = " + empl.age + ", " +
                "phone  = '" + empl.phone + "', " +
                "married = " + empl.married + ", " +
                "team = '" + empl.team + "'" +
                "WHERE id = " + empl.id;

            var msg = "Employee " + empl.name + " was update";
            sqlProcessing._sendQuery(sql, res, false, msg);

        }

    },

    teams: {

        getAllTeams: function(res){

            var sql = "SELECT * FROM teams";
            sqlProcessing._sendQuery(sql, res);

        },

        getTeamsFromStage: function(stage, res){

            var sql = "SELECT * FROM teams WHERE stage = '" + stage + "'";
            sqlProcessing._sendQuery(sql, res);

        },

        createTeam: function(team, res){
            var sql = "INSERT INTO teams (name,stage) VALUES ('" + team.name + "','" + team.stage + "')";
            var msg = "Team " + team.name + " was create";
            sqlProcessing._sendQuery(sql, res, false, msg);
        }

    },

    companies: {

        getAllCompanies: function(res){

            var sql = "SELECT * FROM company";
            sqlProcessing._sendQuery(sql, res);

        }

    },

    locations: {

        getAllLocations: function(res){

            var sql = "SELECT * FROM location";
            sqlProcessing._sendQuery(sql, res);

        }

    },

    stages: {

        getAllStages: function(res){

            var sql = "SELECT * FROM stages";
            sqlProcessing._sendQuery(sql, res);

        }

    }

};

sqlProcessing._loadAllData(glData);